<?php
/**
 * Excel 数据导入
 *
 */

namespace dragon\Excel;

use PHPExcel_Reader_Excel2007;
use PHPExcel_Reader_Excel5;
use PHPExcel;
use think\Exception;

class Excel {

    // 获取 Excel 数据
    public static function get($file, $fieldConfig = array()) {

        //实例化PHPExcel类
        $PHPExcel = new PHPExcel();
        //默认用excel2007读取excel，若格式不对，则用之前的版本进行读取
        $PHPReader = new PHPExcel_Reader_Excel2007();
        if (!$PHPReader->canRead($file)) {
            $PHPReader = new PHPExcel_Reader_Excel5();
            if (!$PHPReader->canRead($file)) {
                throw new Exception('no Excel');
            }
        }

        //读取Excel文件
        $PHPExcel = $PHPReader->load($file);
        //读取excel文件中的第一个工作表
        $sheets = $PHPExcel->getAllSheets();
        $excelData = array();
        foreach ($sheets as $index => $sheet) {
            $PHPExcel->setActiveSheetIndex($index);
            // 获取sheet名称
            $title = $sheet->getTitle();

            //取得最大的列号
            $maxColumn = $sheet->getHighestColumn();
            //取得最大的行号
            $maxRow = $sheet->getHighestRow();
            $letters = explode(',', 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z');
            $cols = array();

            $prefix = '';
            col_append:
            foreach($letters as $letter) {
                $cols[] = $item = $prefix.$letter;
                if($item == $maxColumn) break;

                if($letter == 'Z') {
                    $prefix = $cols[ $prefix ? (array_search($prefix, $cols) + 1) : 0];
                    goto col_append;
                }
            }

            // 列之间的对应关系
            $colConfig = array();
            // 表单数据
            $sheetData = array();
            //从第二行开始读取数据,第一行是列名
            for ($currentRow = 1; $currentRow <= $maxRow; $currentRow++) {
                $data = [];
                if(!$colConfig) {
                    // 获取列之间的对应关系
                    for($i = 0; $i < count($cols); $i++) {
                        $col = $cols[$i];
                        $val = $PHPExcel->getActiveSheet()->getCell($col . $currentRow)->getValue();
                        $val = trim($val);
                        $tagName = array_search($val, $fieldConfig);
                        if(!$tagName) continue;
                        if(!$tagName && $i == 0) break;

                        $colConfig[$col] = $tagName;
                        if($col == $maxColumn) break;
                    }
                } else {
                    // 获取数据
                    for($i = 0; $i < count($cols); $i++) {
                        $col = $cols[$i];
                        if(!isset($colConfig[$col])) continue;

                        $filed = $colConfig[$col];

                        $val = $PHPExcel->getActiveSheet()->getCell($cols{$i} . $currentRow)->getValue();
                        $val = trim($val);

                        $data[$filed] = $val;
                        if($col == $maxColumn) break;
                    }
                }


                if(!$data) continue;

                $sheetData[] = $data;
            }

            if(!$sheetData) continue;

            $excelData[$index] = $sheetData;
        }

        return $excelData;
    }
}